﻿using System.Collections.Generic;
using System.Data.SqlClient;
using DataAssociations.Repository.Connection.RDBMSSchemaExplorer.Interfaces;
using System;
using MySql.Data.MySqlClient;

namespace DataAssociations.Repository.Connection.RDBMSSchemaExplorer.SchemaExplorer
{
    public class MySqlRelationalSchemaExplorer : IRelationalSchemaExplorer
    {
        private const string _tableForeignKeysQuery =
            "SELECT DISTINCT FK.TABLE_NAME as FK_Table, CU.COLUMN_NAME as FK_Column " +
            "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS " +
            "C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK " +
            "ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME " +
            "INNER JOIN " +
            "(SELECT FK.TABLE_NAME AS FK_TABLE, COUNT(*) as ConstraintNumber " +
            "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C " +
            "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK " +
            "ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME " +
            "GROUP BY  FK.TABLE_NAME HAVING COUNT(*) >= 2) AS ManyToManyTables " +
            "ON  FK.TABLE_NAME = ManyToManyTables.FK_TABLE " +
            "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK " +
            "ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME " +
            "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU " +
            "ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME " +
            "INNER JOIN " +
            "(SELECT i1.TABLE_NAME, i2.COLUMN_NAME " +
            "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 " +
            "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 " +
            "ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME " +
            "WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME = PK.TABLE_NAME";

        #region IRelationalSchemaExplorer Members

        public string FindPrimaryKeyColumnNames(string connectionString, string tableName)
        {
            //using (var connection = new SqlConnection(connectionString))
            //{
            //    connection.Open();
            //    var cmd = new SqlCommand(string.Format("{0}'{1}'", _primaryKeyQuery, tableName), connection);
            //    using (SqlDataReader reader = cmd.ExecuteReader())
            //    {
            //        if (reader.Read())
            //        {
            //            return reader[0].ToString();
            //        }
            //        return "";
            //    }
            //}
            throw new NotImplementedException();
        }

        public IList<TableForeignKeyData> BuildForeignKeyConstraintsForDatabase(string connectionString)
        {
            using (var connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                var cmd = new MySqlCommand(_tableForeignKeysQuery, connection);
                var foreignKeysDictionary = new Dictionary<string, List<string>>();
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var foreignKeyTableName = (string) reader["FK_Table"];

                        if (!foreignKeysDictionary.ContainsKey(foreignKeyTableName))
                            foreignKeysDictionary.Add(foreignKeyTableName,
                                                      new List<string> {(string) reader["FK_Column"]});
                        else
                            foreignKeysDictionary[foreignKeyTableName].Add((string) reader["FK_Column"]);
                    }
                    connection.Close();
                    var tableForeignKeyData = new List<TableForeignKeyData>();
                    foreach (var foreignKeyElement in foreignKeysDictionary)
                    {
                        tableForeignKeyData.Add(new TableForeignKeyData
                                         {
                                             TableName = foreignKeyElement.Key,
                                             ForeignKeys = foreignKeyElement.Value
                                         });
                    }
                    return tableForeignKeyData;
                }
            }
        }

        #endregion
    }
}